package com.wxtf.platform.main.sygl;

import java.io.File;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: faa_birth_recordAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.faa_birth_recordAction</li>
 * <li>Description: 独生子女父母光荣证登记</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class faa_birth_recordAction extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(faa_birth_recordAction.class);

	public faa_birth_recordAction(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			}
			else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if ("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			} else if ("listAll".equals(this.getMethodName())) {// 选人列表
				msg = this.listAll(this.getLogicData());
				return msg;
			}else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}
	String[] fields ={"tbefbr_id","tbefbr_type","tbfi_id","org_id","tbefbr_mname","tbefbr_midcard","tbefbr_mdepartment",
			"tbefbr_fname","tbefbr_fidcard","tbefbr_fdepartment","tbefbr_married_time","tbefbr_marriage_certificate",
			"tbefbr_agree_birth_btime","tbefbr_agree_birth_etime","tbefbr_memo",
			"reserve1", "reserve2", "reserve3", "reserve4", "reserve5", "create_by", "create_time",
			"last_modify_by", "last_modify_time" };

	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		sql=" from tbe_faa_birth_record t where 1=1";//所有已婚的，未婚的去掉

		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				sql += " and t.org_id in (" + user.getCurSubOrgId() + ") ";
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串

				String type = jsonObject.getString("tbe_name");//类别
				String father = jsonObject.getString("father");//father
				String mother = jsonObject.getString("mother");//mother
				String time1 = jsonObject.getString("time1");//生育指标批准起始时间
				String time2 = jsonObject.getString("time2");//生育指标批准截止时间
				
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and t.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				
				if(StringUtils.isNotBlank(type)){
					if(!type.equals("2") && type!= "" ){
						buff.append(" and t.tbefbr_type = '").append(type).append("'");
					}
				}
				if(StringUtils.isNotBlank(father)){
					buff.append(" and t.tbefbr_mname like '%").append(father).append("%'");
				}
				if(StringUtils.isNotBlank(mother)){
					buff.append(" and t.tbefbr_fname like '%").append(mother).append("%'");
				}
				if(StringUtils.isNotBlank(time1)){
					buff.append(" and t.tbefbr_agree_birth_btime >= '").append(time1).append("'");
				}
				if(StringUtils.isNotBlank(time2)){
					buff.append(" and t.tbefbr_agree_birth_etime <= '").append(time2).append(" 23:59:59'");
				}
				sql += buff.toString();
			}

		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by t." + sortname + " " + sortorder;
		}else{
			sql+=" order by last_modify_time desc";
		}
		
		
		String countSql = "select count(*) " + sql;
		String querySql = "SELECT tbefbr_id,tbefbr_type,tbfi_id,org_id,tbefbr_mname,tbefbr_midcard,tbefbr_mdepartment,tbefbr_fname,tbefbr_fidcard,tbefbr_fdepartment,tbefbr_married_time,tbefbr_marriage_certificate,tbefbr_agree_birth_btime,tbefbr_agree_birth_etime,tbefbr_memo,reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}	
	
	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbfi_id = jsonObject.getString("tbfi_id");
		
		//一个家庭一种生育证明只能开一次，不可重复添加
		String querySql = "SELECT tbefbr_type from tbe_faa_birth_record where tbfi_id = ?";

		List<Object> list = AccessDataBaseUtil.query4List(querySql,new Object[] {tbfi_id});
		int type1=0;int type2=0;
		if(list.size()>0)
		{
			for (int i = 0; i < list.size(); i++)
			{
				if(list.get(i).toString().equals("0"))
				{
					type1++;
				}
				if(list.get(i).toString().equals("1"))
				{
					type2++;
				}
			}
			
		}
		if(type1>0&&type2>0)
		{
			//提示不允许添加
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了一胎生育证明和再生育证明，不允许再次添加\"}";
		}
		else if(type1>0&&type2==0)
		{
			//提示允许添加计划内二胎
			if(jsonObject.getString("tbefbr_type").equals("0"))
			{
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了一胎生育证明，不允许再次添加一胎生育证明\"}";
			}

		}
		else if(type1==0&&type2>0)
		{
			if(jsonObject.getString("tbefbr_type").equals("1"))
			{
				//提示允许添加计划内一胎
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了再生育证明，不允许再次添加再生育证明\"}";
			}

		}
		
		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		buff.append("insert into tbe_faa_birth_record(tbefbr_id");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
		}
		buff.append(") values(");
		obj[0] = AccessDataBaseUtil.getPKUUID();
		String curTime = FormatUtil.getCurentDateTime();
		obj[20] = user.getUserId();
		obj[21] = curTime;
		obj[22] = user.getUserId();
		obj[23] = curTime;
		
		for(int i=0; i<obj.length; i++){
			buff2.append("'").append(obj[i]).append("',");
		}
		buff2.deleteCharAt(buff2.length() - 1);
		String sql1 = buff.append(buff2.toString()).append(")").toString();
		AccessDataBaseUtil.executeSQLUpdate(sql1);
		return "\"保存成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbfi_id = jsonObject.getString("tbfi_id");
		
		//一个家庭一种生育证明只能开一次，不可重复添加
		String querySql = "SELECT tbefbr_type from tbe_faa_birth_record where tbfi_id = ? and tbefbr_id <> '" + jsonObject.getString("tbefbr_id") + "'";

		List<Object> list = AccessDataBaseUtil.query4List(querySql,new Object[] {tbfi_id});
		int type1=0;int type2=0;
		if(list.size()>0)
		{
			for (int i = 0; i < list.size(); i++)
			{
				if(list.get(i).toString().equals("0"))
				{
					type1++;
				}
				if(list.get(i).toString().equals("1"))
				{
					type2++;
				}
			}
			
		}
		if(type1>0&&type2>0)
		{
			//提示不允许添加
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了一胎生育证明和再生育证明，不允许再次添加\"}";
		}
		else if(type1>0&&type2==0)
		{
			//提示允许添加计划内二胎
			if(jsonObject.getString("tbefbr_type").equals("0"))
			{
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了一胎生育证明，不允许再次添加一胎生育证明\"}";
			}

		}
		else if(type1==0&&type2>0)
		{
			if(jsonObject.getString("tbefbr_type").equals("1"))
			{
				//提示允许添加计划内一胎
				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"已经添加了再生育证明，不允许再次添加再生育证明\"}";
			}

		}
		
		StringBuffer buff = new StringBuffer();
		buff.append("update tbe_faa_birth_record set ");
		Object[] obj = new Object[fields.length];
		for(int j = 1; j < fields.length; j++) {
			obj[j - 1] = jsonObject.getString(fields[j]);
			buff.append(fields[j]).append(" = ?,");
		}
		buff.deleteCharAt(buff.length() - 1).append(" where tbefbr_id = ?");
		obj[21] = user.getUserId();
		obj[22] = curTime;
		obj[23] = jsonObject.getString("tbefbr_id");
		
		AccessDataBaseUtil.executeSQLUpdate(buff.toString(), obj);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		sql = "delete from tbe_faa_birth_record where tbefbr_id = ?";
		
		AccessDataBaseUtil.deleteByIds(sql, ids);
		return "\"删除成功\"";
	}
	
	///筛选选人信息
	public Object listAll(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String fathername = jsonObject.getString("fathername");
		String mothername = jsonObject.getString("mothername");
		//妻子办理
		sql=" from(SELECT f.tbfi_id,e.org_id,e.tbe_name as tbefbr_mname,e.tbe_idcard as tbefbr_midcard,"+
		" e.reserve1 as tbefbr_mdepartment,f.tbfi_spouse_name as tbefbr_fname,f.tbfi_spouse_idcard as tbefbr_fidcard,f.tbfi_spouse_department as tbefbr_fdepartment,"+
		" tbe_married_time as tbefbr_married_time,tbe_marriage_certificate as tbefbr_marriage_certificate,e.tbe_marital_status,e.reserve1"+
		" from tb_family_base f LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id where f.tbfi_valid='1' and e.tbe_employment_forms <> '31' and tbe_status not in('102-5','102-3')) t where 1=1 and " +
		" t.org_id in (" + user.getCurSubOrgId() + ") and tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320')";
		//所有已婚的，未婚的去掉,离职，死亡，劳务用工的除外
		if(StringUtils.isNotBlank(fathername)){
			sql += " and tbefbr_mname like '%" + fathername + "%'";
		}
		if(StringUtils.isNotBlank(mothername)){
			sql += " and tbefbr_fname like '%" + mothername + "%'";
		}
		
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by t." + sortname + " " + sortorder;
		}
		String countSql = "select count(*)" + sql;
		String querySql = "select tbfi_id,org_id,tbefbr_mname,tbefbr_midcard,tbefbr_mdepartment,tbefbr_fname,tbefbr_fidcard,tbefbr_fdepartment,tbefbr_married_time,tbefbr_marriage_certificate,reserve1 " + sql;
		//System.out.println(querySql);
		String[] field1 ={"tbfi_id","org_id","tbefbr_mname","tbefbr_midcard","tbefbr_mdepartment",
				"tbefbr_fname","tbefbr_fidcard","tbefbr_fdepartment","tbefbr_married_time","tbefbr_marriage_certificate","reserve1"};
		return AccessDataBaseUtil.query4SQLPagging(field1, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}

	/**
	 * 描述：expData 人员导出
	 * 
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn
	 * @author wangli
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String strexp="select case tbefbr_type when '0' then '一胎生育证明' when '1' then '二胎生育证明' else '无' end,reserve1,tbefbr_mname,tbefbr_midcard,tbefbr_mdepartment,tbefbr_fname,tbefbr_fidcard,tbefbr_fdepartment,tbefbr_married_time,tbefbr_marriage_certificate,tbefbr_agree_birth_btime,tbefbr_memo from tbe_faa_birth_record t where 1=1 ";
		//所有已婚的，未婚的去掉
		String orgIds = jsonObject.getString("orgIds");//机构ID串
		String type = jsonObject.getString("tbe_name");//类别
		String father = jsonObject.getString("father");//father
		String mother = jsonObject.getString("mother");//mother
		String time1 = jsonObject.getString("time1");//生育指标批准起始时间
		String time2 = jsonObject.getString("time2");//生育指标批准截止时间
		
		StringBuffer buff = new StringBuffer();
		if(StringUtils.isNotBlank(orgIds)){
			buff.append(" and t.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
		}
		
		if(StringUtils.isNotBlank(type)){
			if(!type.equals("2") && type!= "" ){
				buff.append(" and t.tbefbr_type = '").append(type).append("'");
			}
		}
		if(StringUtils.isNotBlank(father)){
			buff.append(" and t.tbefbr_mname like '%").append(father).append("%'");
		}
		if(StringUtils.isNotBlank(mother)){
			buff.append(" and t.tbefbr_fname like '%").append(mother).append("%'");
		}
		if(StringUtils.isNotBlank(time1)){
			buff.append(" and t.tbefbr_agree_birth_btime >= '").append(time1).append("'");
		}
		if(StringUtils.isNotBlank(time2)){
			buff.append(" and t.tbefbr_agree_birth_etime <= '").append(time2).append(" 23:59:59'");
		}

		strexp += buff.toString();

		List<Object[]> list = AccessDataBaseUtil.query(strexp, null);

		String url = this.getRequest().getSession().getServletContext()
				.getRealPath("/jsp/app/xls/");
		String model = url + "/model/birth.xls";
		String file = url + "/file/birth.xls";
		String rfile = "jsp/app/xls/file/birth.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
//		if(list.size()>0)
//		{
//			for(int i=0;i<list.size();i++)
//			{
//				if(list.get(i)[0].toString().equals("0"))//核算标准
//				{
//					list.get(i)[0]="一胎生育证明";
//				}
//				else if(list.get(i)[0].toString().equals("1"))
//				{
//					list.get(i)[0]="再生育证明";
//				}
//				else{list.get(i)[0]="无";}
//			}
//		}
		if (list.size() > 0) {
			XLSUtils.writeXls(list, fto, "生育证明登记一览表", 1);// 写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}

		return "\"" + rfile + "\"";
	}
	
	
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}


